IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Employee]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetEmployee
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[Employee] (
   ID   Varchar(30),
   [EmployeeId]     Varchar(50),
   [EmployeeType]     Varchar(50),
   [FName]     Varchar(50),
   [LName]     Varchar(50),
   [MobilePhone]     Varchar(50),
   [HomePhone]     Varchar(50),
   [OfficePhone]     Varchar(50),
   [Email]     Varchar(50),
   [Address1]     Varchar(50),
   [Address2]     Varchar(50),
   [City]     Varchar(50),
   [State]     Varchar(50),
   [ZipCode]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Employee TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetEmployee') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetEmployee
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_GetEmployee
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetEmployee
@vchID VARCHAR(30)
AS
   SELECT
       ID,
       EmployeeId,
       EmployeeType,
       FName,
       LName,
       MobilePhone,
       HomePhone,
       OfficePhone,
       Email,
       Address1,
       Address2,
       City,
       State,
       ZipCode
   FROM Employee
   WHERE ID=@vchID
GO

GRANT EXEC ON dbo.SP_GetEmployee TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetEmployee') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetEmployee
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetEmployee
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetEmployee
@vchID Varchar(30),
@vchEmployeeId    Varchar(50),
@vchEmployeeType    Varchar(50),
@vchFName    Varchar(50),
@vchLName    Varchar(50),
@vchMobilePhone    Varchar(50),
@vchHomePhone    Varchar(50),
@vchOfficePhone    Varchar(50),
@vchEmail    Varchar(50),
@vchAddress1    Varchar(50),
@vchAddress2    Varchar(50),
@vchCity    Varchar(50),
@vchState    Varchar(50),
@vchZipCode    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     ID
   FROM Employee
   WHERE   (ID = @vchID)
   )

   BEGIN
   INSERT INTO Employee(
       ID,
       EmployeeId,
       EmployeeType,
       FName,
       LName,
       MobilePhone,
       HomePhone,
       OfficePhone,
       Email,
       Address1,
       Address2,
       City,
       State,
       ZipCode
  )
   VALUES (
      @vchID,
       @vchEmployeeId,
       @vchEmployeeType,
       @vchFName,
       @vchLName,
       @vchMobilePhone,
       @vchHomePhone,
       @vchOfficePhone,
       @vchEmail,
       @vchAddress1,
       @vchAddress2,
       @vchCity,
       @vchState,
       @vchZipCode
   )
   END
ELSE  IF EXISTS(SELECT     ID
   FROM Employee
   WHERE   (ID = @vchID)
   )

   BEGIN
   UPDATE Employee
   SET
       EmployeeId = @vchEmployeeId,
       EmployeeType = @vchEmployeeType,
       FName = @vchFName,
       LName = @vchLName,
       MobilePhone = @vchMobilePhone,
       HomePhone = @vchHomePhone,
       OfficePhone = @vchOfficePhone,
       Email = @vchEmail,
       Address1 = @vchAddress1,
       Address2 = @vchAddress2,
       City = @vchCity,
       State = @vchState,
       ZipCode = @vchZipCode
  WHERE (ID = @vchID)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetEmployee for Loan# : ' + CAST (@vchID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetEmployee TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeleteEmployee') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeleteEmployee
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_DeleteEmployee
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeleteEmployee
@vchID VARCHAR(30)
AS
   DELETE FROM Employee  WHERE   (ID = @vchID)

GRANT EXEC ON dbo.SP_DeleteEmployee TO hms_usr
GO

